﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

using EHSubmit.Util;
using EHSubmit.Model;

namespace EHSubmit.Logic
{
    /// <summary>
    /// 管理作业分数、统计情况
    /// </summary>
    public class ScoreManager
    {
        public ScoreManager()
        {
        }


        /// <summary>
        /// 班级课程平均分走势图数据
        /// </summary>
        /// <param name="courseId">课程ID</param>
        /// <returns>该课程每次作业平均分集合</returns>
        public IList<GoalScore> GetCourseScores(string courseId){

            IList<GoalScore> list = new List<GoalScore>();

            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@courseId", SqlDbType.NVarChar, 10);
            parms[0].Value = courseId;

            parms[0].Direction = ParameterDirection.Input;

            SqlDataReader rdr = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "CourseAnalysis", parms);

            while (rdr.Read())
            {
                GoalScore gs = new GoalScore();
                gs.GoalID = (Int32)rdr["goalId"];
                gs.GoalName = (string)rdr["goalName"];
                gs.Max = (Int32)rdr["maxScore"];
                gs.Min = (Int32)rdr["minScore"];
                gs.Average = (Int32)rdr["avgScore"];

                list.Add(gs);
            }

            return list;
        }

        /// <summary>
        /// 学生课程平均分
        /// </summary>
        /// <param name="courseId">课程ID</param>
        /// <returns>学生平均成绩集合</returns>
        public IList<StudentScore> GetStudentAverageScore(string courseId)
        {
            IList<StudentScore> list = new List<StudentScore>();

            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@courseId", SqlDbType.NVarChar, 10);
            parms[0].Value = courseId;

            parms[0].Direction = ParameterDirection.Input;

            SqlDataReader rdr = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "CourseAnalysisOfStudent", parms);

            while (rdr.Read())
            {
                StudentScore score = new StudentScore();
                score.StudentID = (string)rdr["studentId"];
                score.StudentName = (string)rdr["UName"];
                score.Average = (Int32)rdr["avgScore"];

                list.Add(score);
            }

            return list;
        }


        /// <summary>
        /// 某学生的某课程的所有作业成绩(已经明确按goalid排序，方便与统计的班级分数对应)
        /// 返回的数据无需验证有效性（对应goal的作业未交的数据和尚未打分的数据已被过滤）
        /// </summary>
        /// <param name="sid">学生ID</param>
        /// <param name="courseID">课程ID</param>
        /// <returns>作业成绩</returns>
        public IList<HomeworkScore> GetScoresOfCourseByStu(string sid, string courseID)
        {
            IList<HomeworkScore> hwScoresList = new List<HomeworkScore>();
            SqlParameter[] parms = new SqlParameter[2];

            parms[0] = new SqlParameter("@courseID", SqlDbType.NVarChar, 10);
            parms[1] = new SqlParameter("@stuID", SqlDbType.NChar, 13);
            parms[0].Value = courseID;
            parms[1].Value = sid;

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;

            SqlDataReader rdr = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "AnalysisScoresOfCourseByStu", parms);

            while (rdr.Read())
            {
                HomeworkScore hwScore = new HomeworkScore();

                hwScore.ID = (Int32)rdr["WID"];
                hwScore.GoalID = (Int32)rdr["GoalID"];
                hwScore.Score = (Int32)rdr["Scores"];

                hwScoresList.Add(hwScore);
            }

            return hwScoresList;

        }

        /// <summary>
        /// 根据学生ID和goalID查找作业分数
        /// </summary>
        /// <param name="sid">学生ID</param>
        /// <param name="gid">goalID</param>
        /// <returns>相应的作业分数</returns>
        public HomeworkScore GetScoreOfGoal(string sid, int gid)
        {
            string sql = "SELECT WID,GoalID,Scores FROM Homework WHERE GoalID = @GoalID AND StudentID = @StudentID";
            SqlParameter[] parms = new SqlParameter[2];
            HomeworkScore hwScore = new HomeworkScore();

            parms[0] = new SqlParameter("@GoalID", SqlDbType.Int);
            parms[1] = new SqlParameter("@StudentID", SqlDbType.NChar, 13);
            parms[0].Value = gid;
            parms[1].Value = sid;

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;

            using (SqlDataReader rdr = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, parms))
            {

                if (rdr.Read())
                {
                    hwScore.ID = (Int32)rdr["WID"];
                    hwScore.GoalID = (Int32)rdr["GoalID"];
                    hwScore.Score = (Int32)rdr["Scores"];
                }
                else
                {
                    return null;
                }
            }

            return hwScore;
        }
    }
}